﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using System.Xml;
using System.IO;
using System.Collections;
using System.Data.SQLite;
using System.Threading;

namespace Clipping.Utils
{
    /// <summary> 
    /// SQLite数据库的访问方法集合
    /// </summary> 
    internal static class SQLiteHelper
    {
        /// <summary> 
        /// 生成一个命令 
        /// </summary> 
        /// <param name="connection">数据库连接对象</param> 
        /// <param name="commandText">命令文本</param> 
        /// <param name="commandParameters">命令参数</param> 
        /// <returns>SQLite命令</returns> 
        public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
        {
            SQLiteCommand cmd = new SQLiteCommand(commandText, connection); if (commandParameters.Length > 0)
            {
                foreach (SQLiteParameter parm in commandParameters)
                    cmd.Parameters.Add(parm);
            }
            return cmd;
        }

     
        /// <summary> 
        /// 生成一个参数对象
        /// </summary> 
        /// <param name="parameterName">参数名称</param> 
        /// <param name="parameterType">参数类型</param> 
        /// <param name="parameterValue">参数的值</param> 
        /// <returns>SQLiteParameter对象</returns> 
        public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue)
        {
            SQLiteParameter parameter = new SQLiteParameter();
            parameter.DbType = parameterType;
            parameter.ParameterName = parameterName;
            parameter.Value = parameterValue;
            return parameter;
        }

        /// <summary> 
        /// 通过SQL语句，返回DataSet对象的快捷方法
        /// </summary> 
        /// <param name="cn">连接对象</param> 
        /// <param name="commandText">命令文本</param> 
        /// <param name="paramList">参数列表</param> 
        /// <returns>结果集</returns> 
        public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
        {
            SQLiteCommand cmd = cn.CreateCommand();
            cmd.CommandText = commandText;
            SQLiteParameterCollection parms = DeriveParameters(commandText, paramList);
            if (parms != null)
            {
                foreach (SQLiteParameter p in parms)
                    cmd.Parameters.Add(p);
            }
            DataSet ds = new DataSet();

            if (cn.State == ConnectionState.Closed)
                cn.Open();

            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);

            da.Dispose();

            cmd.Dispose();
            return ds;
        }

        /// <summary>
        /// 通过SQL语句进行查询
        /// </summary>
        /// <param name="cn">数据库连接</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paramList">参数集合</param>
        /// <returns>查询结果对象</returns>
        public static object ExecuteScalar(SQLiteConnection cn, string commandText, object[] paramList)
        {
            SQLiteCommand cmd = cn.CreateCommand();
            cmd.Connection = cn;
            cmd.CommandText = commandText;
            SQLiteParameterCollection parms = DeriveParameters(commandText, paramList);
            if (parms != null)
            {
                foreach (SQLiteParameter p in parms)
                    cmd.Parameters.Add(p);
            }
            object result = cmd.ExecuteScalar();
            cmd.Dispose();
            return result;
        }

        /// <summary>
        /// 执行输入的SQLiteCommand操作DataSet
        /// </summary> 
        /// <param name="cmd">完整的输入命令</param> 
        /// <returns>DataSet</returns> 
        public static DataSet ExecuteDataset(SQLiteCommand cmd)
        {
            if (cmd.Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();

            DataSet ds = new DataSet();
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);
            da.Dispose();
            cmd.Connection.Close();
            cmd.Dispose();
            return ds;
        }

        /// <summary> 
        /// 通过事务执行命令得到结果集
        /// </summary> 
        /// <param name="transaction">SQLite事务对象.事务对象包括数据库连接，事务，和命令，这些内容都必须在调用方法前定义好</param> 
        /// <param name="commandText">命令文本</param> 
        /// <param name="commandParameters">命令参数集合</param> 
        /// <returns>结果集</returns> c
        /// <remarks>用户必须检查事务对象并且手动关闭事务连接。</remarks> 
        public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, params SQLiteParameter[] commandParameters)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null)
                throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            foreach (SQLiteParameter parm in commandParameters)
            {
                cmd.Parameters.Add(parm);
            }

            if (transaction.Connection.State == ConnectionState.Closed)
                transaction.Connection.Open();

            DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
            return ds;
        }

        /// <summary> 
        /// 通过事务执行命令得到结果集
        /// </summary> 
        /// <param name="transaction">SQLite事务对象.事务对象包括数据库连接，事务，和命令，这些内容都必须在调用方法前定义好 </param> 
        /// <param name="commandText">命令文本</param> 
        /// <param name="commandParameters">命令参数集合</param> 
        /// <returns>结果集</returns> 
        /// <remarks>用户必须检查事务对象并且手动关闭事务连接。</remarks> 
        public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, object[] commandParameters)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null)
                throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            SQLiteParameterCollection coll = DeriveParameters(cmd.CommandText, commandParameters);
            if (coll != null)
            {
                foreach (SQLiteParameter parm in coll)
                    cmd.Parameters.Add(parm);
            }

            if (transaction.Connection.State == ConnectionState.Closed)
                transaction.Connection.Open();

            DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
            return ds;
        }

        #region UpdateDataset

        /// <summary> 
        /// 对结果集独立执行插入，更新，删除操作
        /// </summary> 
        /// <remarks> 
        /// e.g.:  
        /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); 
        /// </remarks> 
        /// <param name="insertCommand">一条有效的插入语句</param> 
        /// <param name="deleteCommand">一条有效的删除语句</param> 
        /// <param name="updateCommand">一条有效的更新语句</param> 
        /// <param name="dataSet">用来操作的结果集对象</param> 
        /// <param name="tableName">数据库中对应的数据表名</param> 
        public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName)
        {
            if (insertCommand == null)
                throw new ArgumentNullException("insertCommand");
            if (deleteCommand == null)
                throw new ArgumentNullException("deleteCommand");
            if (updateCommand == null)
                throw new ArgumentNullException("updateCommand");
            if (tableName == null || tableName.Length == 0)
                throw new ArgumentNullException("tableName");
            // 创建一个SQLite数据适配器， 并且在用完之后释放。
            using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter())
            {
                // 设置数据适配器的命令
                dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                dataAdapter.DeleteCommand = deleteCommand;
                // 从数据源里更新dataSet
                dataAdapter.Update(dataSet, tableName);
                // 将所有更改提交
                dataSet.AcceptChanges();
            }
        }

        #endregion

        /// <summary> 
        /// 返回IDataReader对象的快捷方法
        /// 附注：在阅读器关闭之后，你必须显式的调用命令的Dispose方法关闭命令的连接。
        /// 这么做的原因是IDataReader底层没有连接属性
        /// </summary> 
        /// <param name="cmd">命令对象</param> 
        /// <param name="commandText">命令文本</param> 
        /// <param name="paramList">参数集合</param> 
        /// <returns>只进结果集流</returns> 
        public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList)
        {
            if (cmd.Connection == null)
                throw new ArgumentException("Command must have live connection attached.", "cmd");

            cmd.CommandText = commandText;
            SQLiteParameterCollection parms = DeriveParameters(commandText, paramList);
            if (parms != null)
            {
                foreach (SQLiteParameter p in parms)
                    cmd.Parameters.Add(p);
            }

            if (cmd.Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();

            IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return rdr;
        }

        /// <summary>
        /// 无返回结果集的操作
        /// </summary>
        /// <param name="cn">数据库连接</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="paramList">参数列表</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(SQLiteConnection cn, string commandText, object[] paramList)
        {
            SQLiteCommand cmd = cn.CreateCommand();
            cmd.CommandText = commandText;
            SQLiteParameterCollection parms = DeriveParameters(commandText, paramList);
            if (parms != null)
            {
                foreach (SQLiteParameter p in parms)
                    cmd.Parameters.Add(p);
            }

            if (cn.State == ConnectionState.Closed)
                cn.Open();

            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return result;
        }

        /// <summary> 
        /// 通过事务进行无返回结果集的操作
        /// </summary> 
        /// <param name="transaction">SQLite事务对象.事务对象包括数据库连接，事务，和命令，这些内容都必须在调用方法前定义好。</param> 
        /// <param name="commandText">命令文本</param> 
        /// <param name="paramList">参数列表</param> 
        /// <returns>受影响的行数</returns> 
        /// <remarks>用户必须检查事务对象并且手动关闭事务连接。</remarks> 
        public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, object[] paramList)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null)
                throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            SQLiteParameterCollection coll = DeriveParameters(cmd.CommandText, paramList);
            if (coll != null)
            {
                foreach (SQLiteParameter parm in coll)
                    cmd.Parameters.Add(parm);
            }

            if (transaction.Connection.State == ConnectionState.Closed)
                transaction.Connection.Open();

            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return result;
        }

        /// <summary> 
        /// 无返回结果集的操作
        /// </summary> 
        /// <param name="cmd">命令对象</param> 
        /// <returns>受影响的行数</returns> 
        public static int ExecuteNonQuery(IDbCommand cmd)
        {
            if (cmd.Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();
            int result = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            cmd.Dispose();
            return result;
        }

        /// <summary> 
        /// 通过完整的命令操作XmlReader对象
        /// </summary> 
        /// <param name="command">SQLite命令</param> 
        /// <returns>XmlReader</returns> 
        public static XmlReader ExecuteXmlReader(IDbCommand command)
        {
            // 如果比较的话就打开连接，但是要记得使用之后关闭它        
            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
            }

            // get a data adapter 
            SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)command);
            DataSet ds = new DataSet();
            // fill the data set, and return the schema information 
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.Fill(ds);

            // convert our dataset to XML 
            StringReader stream = new StringReader(ds.GetXml());
            command.Connection.Close();
            // convert our stream of text to an XmlReader 
            return new XmlTextReader(stream);
        }

        /// <summary> 
        /// Parses parameter names from SQL Statement, assigns values from object array , 
        /// and returns fully populated ParameterCollection. 
        /// </summary> 
        /// <param name="commandText">Sql Statement with "@param" style embedded parameters</param> 
        /// <param name="paramList">object[] array of parameter values</param> 
        /// <returns>SQLiteParameterCollection</returns> 
        /// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same 
        ///order as parameter names appear in SQL statement.</remarks> 
        public static SQLiteParameterCollection DeriveParameters(string commandText, object[] paramList)
        {
            if (paramList == null) return null;

            SQLiteParameterCollection coll = DeriveParameters(commandText, paramList); ;
            string parmString = commandText.Substring(commandText.IndexOf("@"));
            // pre-process the string so always at least 1 space after a comma. 
            parmString = parmString.Replace(",", " ,");
            // get the named parameters into a match collection 
            string pattern = @"(@)\S*(.*?)\b";

            Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
            MatchCollection mc = ex.Matches(parmString);
            string[] paramNames = new string[mc.Count];
            int i = 0;
            foreach (Match m in mc)
            {
                paramNames[i] = m.Value;
                i++;
            }
            // 判断参数类型 
            int j = 0;
            Type t = null;
            foreach (object o in paramList)
            {
                t = o.GetType();
                SQLiteParameter parm = new SQLiteParameter();
                switch (t.ToString())
                {
                    case ("DBNull"):
                    case ("Char"):
                    case ("SByte"):
                    case ("UInt16"):
                    case ("UInt32"):
                    case ("UInt64"):
                        throw new SystemException("Invalid data type");
                    case ("System.String"):
                        parm.DbType = DbType.String;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (string)paramList[j];
                        coll.Add(parm);
                        break;
                    case ("System.Byte[]"):
                        parm.DbType = DbType.Binary;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (byte[])paramList[j];
                        coll.Add(parm);
                        break;
                    case ("System.Int32"):
                        parm.DbType = DbType.Int32;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (int)paramList[j];
                        coll.Add(parm);
                        break;
                    case ("System.Boolean"):
                        parm.DbType = DbType.Boolean;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (bool)paramList[j];
                        coll.Add(parm);
                        break;
                    case ("System.DateTime"):
                        parm.DbType = DbType.DateTime;
                        parm.ParameterName = paramNames[j];
                        parm.Value = Convert.ToDateTime(paramList[j]);
                        coll.Add(parm);
                        break;
                    case ("System.Double"):
                        parm.DbType = DbType.Double;
                        parm.ParameterName = paramNames[j];
                        parm.Value = Convert.ToDouble(paramList[j]);
                        coll.Add(parm);
                        break;
                    case ("System.Decimal"):
                        parm.DbType = DbType.Decimal;
                        parm.ParameterName = paramNames[j];
                        parm.Value = Convert.ToDecimal(paramList[j]);
                        break;
                    case ("System.Guid"):
                        parm.DbType = DbType.Guid;
                        parm.ParameterName = paramNames[j];
                        parm.Value = (System.Guid)(paramList[j]);
                        break;
                    case ("System.Object"):
                        parm.DbType = DbType.Object;
                        parm.ParameterName = paramNames[j];
                        parm.Value = paramList[j];
                        coll.Add(parm);
                        break;
                    default:
                        throw new SystemException("Value is of unknown data type");
                }
                // end switch 

                j++;
            }
            return coll;
        }

        /// <summary> 
        /// Executes non query typed params from a DataRow 
        /// </summary> 
        /// <param name="command">执行命令</param> 
        /// <param name="dataRow">数据行</param> 
        /// <returns>受影响的行数</returns> 
        public static int ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow)
        {
            int retVal = 0;
            // 如果数据行内有数据，则存储过程必须初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 设置参数值
                AssignParameterValues(command.Parameters, dataRow);
                retVal = ExecuteNonQuery(command);
            }
            else
            {
                retVal = ExecuteNonQuery(command);
            }
            return retVal;
        }

        #region 内部方法

        /// <summary> 
        /// This method assigns dataRow column values to an IDataParameterCollection 
        /// </summary> 
        /// <param name="commandParameters">The IDataParameterCollection to be assigned values</param> 
        /// <param name="dataRow">The dataRow used to hold the command's parameter values</param> 
        /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception> 
        internal static void AssignParameterValues(IDataParameterCollection commandParameters, DataRow dataRow)
        {
            if (commandParameters == null || dataRow == null)
            {
                // 无数据就什么都不做
                return;
            }
            DataColumnCollection columns = dataRow.Table.Columns;
            int i = 0;
            // 设置参数值

            foreach (IDataParameter commandParameter in commandParameters)
            {
                // 检查参数名
                if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1)
                    throw new InvalidOperationException(string.Format("Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.", i, commandParameter.ParameterName));
                if (columns.Contains(commandParameter.ParameterName))
                    commandParameter.Value = dataRow[commandParameter.ParameterName];
                else if (columns.Contains(commandParameter.ParameterName.Substring(1)))
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                i++;
            }
        }

        /// <summary> 
        /// This method assigns dataRow column values to an array of IDataParameters 
        /// </summary> 
        /// <param name="commandParameters">Array of IDataParameters to be assigned values</param> 
        /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param> 
        /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception> 
        private static void AssignParameterValues(IDataParameter[] commandParameters, DataRow dataRow)
        {
            if ((commandParameters == null) || (dataRow == null))
            {
                // Do nothing if we get no data 
                return;
            }
            DataColumnCollection columns = dataRow.Table.Columns;
            int i = 0;
            // Set the parameters values 
            foreach (IDataParameter commandParameter in commandParameters)
            {
                // Check the parameter name 
                if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1)
                    throw new InvalidOperationException(string.Format("Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.", i, commandParameter.ParameterName));
                if (columns.Contains(commandParameter.ParameterName))
                    commandParameter.Value = dataRow[commandParameter.ParameterName];
                else if (columns.Contains(commandParameter.ParameterName.Substring(1)))
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                i++;
            }
        }

        /// <summary> 
        /// This method assigns an array of values to an array of IDataParameters 
        /// </summary> 
        /// <param name="commandParameters">Array of IDataParameters to be assigned values</param> 
        /// <param name="parameterValues">Array of objects holding the values to be assigned</param> 
        /// <exception cref="System.ArgumentException">Thrown if an incorrect number of parameters are passed.</exception> 
        private static void AssignParameterValues(IDataParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                // Do nothing if we get no data 
                return;
            }
            // We must have the same number of values as we pave parameters to put them in 
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }
            // Iterate through the IDataParameters, assigning the values from the corresponding position in the  
            // value array 
            for (int i = 0, j = commandParameters.Length, k = 0; i < j; i++)
            {
                if (commandParameters[i].Direction != ParameterDirection.ReturnValue)
                {
                    // If the current array value derives from IDataParameter, then assign its Value property 
                    if (parameterValues[k] is IDataParameter)
                    {
                        IDataParameter paramInstance;
                        paramInstance = (IDataParameter)parameterValues[k];
                        if (paramInstance.Direction == ParameterDirection.ReturnValue)
                        {
                            paramInstance = (IDataParameter)parameterValues[++k];
                        }
                        if (paramInstance.Value == null)
                        {
                            commandParameters[i].Value = DBNull.Value;
                        }
                        else
                        {
                            commandParameters[i].Value = paramInstance.Value;
                        }
                    }
                    else if (parameterValues[k] == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[i].Value = parameterValues[k];
                    }
                    k++;
                }
            }
        }

        #endregion

    }

    public partial class DBConnection : IDisposable
    {
        #region 用户库

        /// <summary>
        /// 到用户信息库的连接
        /// </summary>
        private readonly static SQLiteConnection UserConn = new SQLiteConnection();
        /// <summary>
        /// 用户库锁标志
        /// </summary>
        private static bool IsUserLock = false;
        /// <summary>
        /// 初始化用户库标志
        /// </summary>
        private static bool IsUserFirst = true;

        


      

        #endregion

     

        #region 实例成员

        private SQLiteConnection conn;

        /// <summary>
        /// 用指定连接初始化一个连接实例
        /// </summary>
        /// <param name="conn">指定的数据库连接</param>
        private DBConnection(SQLiteConnection conn)
        {
            this.conn = conn;
        }

        /// <summary>
        /// 为连接对象解锁
        /// </summary>
        public void Dispose()
        {
            //解锁
            if (this.conn!=null && this.conn.Equals(UserConn))
            {
                IsUserLock = false;
            }
        }

        #region 数据库操作

        /// <summary> 
        /// 通过SQL语句，操作DataSet对象的快捷方法
        /// </summary> 
        /// <param name="commandText">命令文本</param> 
        /// <param name="paramList">参数列表</param> 
        /// <returns>结果集</returns> 
        public DataSet ExecuteDataSet(string commandText, object[] paramList)
        {
            return SQLiteHelper.ExecuteDataSet(conn, commandText, paramList);
        }

        /// <summary>
        /// 无返回结果集的操作
        /// </summary>
        /// <param name="commandText">命令文本</param>
        /// <param name="paramList">参数列表</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string commandText, object[] paramList)
        {
            return SQLiteHelper.ExecuteNonQuery(conn, commandText, paramList);
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="commandText">命令文本</param>
        /// <param name="paramList">参数列表</param>
        /// <returns>查询结果</returns>
        public object ExecuteScalar(string commandText, object[] paramList)
        {
            return SQLiteHelper.ExecuteScalar(conn, commandText, paramList);
        }

        #endregion

        #endregion
    }

}
